import time
from tkinter import *
from tkinter import ttk
import pymysql
from matplotlib.figure import Figure
import matplotlib
from matplotlib.backends.backend_tkagg import FigureCanvasTkAgg
import sz_count

# 定义一个第二个窗口的方法
def win_two():
    window2 = Tk()
    window2.title("个人记账系统")
    # 设定窗口的大小(长 * 宽)
    # 将窗口固定在屏幕中间
    width = 450
    height = 450
    screenwidth = window2.winfo_screenwidth()
    screenheight = window2.winfo_screenheight()
    alignstr = '%dx%d+%d+%d' % (width, height, (screenwidth - width) / 2, (screenheight - height) / 3)
    window2.geometry(alignstr)

    # 设置窗口大小不可改变
    window2.resizable(width=False, height=False)
    window2.iconphoto(False, PhotoImage(file='tb.png'))
    # 背景颜色
    window2.configure(background='ivory')

    # 单击按钮弹出窗口
    B1 = Button(
        window2,
        text="收入管理",  # 文本
        bg='thistle',  # 背景颜色
        fg='black',  # 文本颜色
        relief='raised',  # 边框的3D样式
        bd=1,  # 边框的大小
        width=11,
        height=5,
        activeforeground="red",  # 它表示字体颜色的按钮，当鼠标悬停在按钮上改变前景色
        font=('Arial', 20),
        borderwidth=7,
        command=shou_ru,  # 点击事件
    )
    B1.place(x=15, y=15)
    B2 = Button(
        window2,
        text="支出管理",  # 文本
        bg='lightgreen',  # 背景颜色
        fg='black',  # 文本颜色
        relief='raised',  # 边框的3D样式
        bd=1,  # 边框的大小
        width=11,
        height=5,
        activeforeground="red",  # 它表示字体颜色的按钮，当鼠标悬停在按钮上改变前景色
        font=('Arial', 20),
        borderwidth=7,
        command=zhi_chu,  # 点击事件
    )
    B2.place(x=225, y=15)

    B5 = Button(
        window2,
        text="收支统计(图表)",  # 文本
        bg='lavender',  # 背景颜色
        fg='black',  # 文本颜色
        relief='raised',  # 边框的3D样式
        bd=1,  # 边框的大小
        width=24,
        height=5,
        activeforeground="red",  # 它表示字体颜色的按钮，当鼠标悬停在按钮上改变前景色
        font=('Arial', 20),
        borderwidth=7,  # 边框样式
        command=sz_count.sz_tb,  # 点击事件
    )
    B5.place(x=15, y=230)

    window2.mainloop()





# 收入删除数据库
def sr_delete():
    # 打开数据库连接
    db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True', charset='utf8')
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    cursor.execute("DELETE FROM INCOME WHERE SR_SJ ='" + str_sj + "'")
    db.commit()
    db.close()



# 单击事件
def treeviewClick():#单击

    global treeviewClick,str_sj
    for item in tree.selection():
        item_text3 = tree.item(item, "values")
        tree.delete(item)
        str_sj = item_text3[3]# 输出所选行的第4列的值-时间
        sr_delete()



# 定义收入排序
def sr_treeview_sort_column(tv, col, reverse):  # Treeview、列名、排列方式
    l = [(tv.set(k, col), k) for k in tv.get_children('')]
    l.sort(reverse=reverse)  # 排序方式
    for index, (val, k) in enumerate(l):  # 根据排序后索引移动
        tv.move(k, '', index)
    tv.heading(col, command=lambda: sr_treeview_sort_column(tv, col, not reverse))  # 重写标题，使之成为再点倒序的标题



# 收入查询
def sr_select():
    window_sr_select = Tk()
    window_sr_select.title("收入查询")  # 收入窗口

    # 固定窗口位置
    width = 1080
    height = 455
    screenwidth = window_sr_select.winfo_screenwidth()
    screenheight = window_sr_select.winfo_screenheight()
    alignstr = '%dx%d+%d+%d' % (width, height, (screenwidth - width) / 2, (screenheight - height) / 1.5)
    window_sr_select.geometry(alignstr)

    # 设置窗口大小不可改变
    window_sr_select.resizable(width=False, height=False)
    # 设置窗口颜色
    window_sr_select.configure(background='azure')

    # 表格框
    style = ttk.Style(window_sr_select)
    style.theme_use("clam")
    style.configure("Treeview", background="black",
                    fieldbackground="azure", foreground="azure")
    tree = ttk.Treeview(window_sr_select, show="headings",
                        columns=("标题", "金额", "收入方式", "时间", "备注"), height=23,
                        selectmode="browse")
    tree.place(x=10, y=10)

    # 设置滚动条
    vsb = ttk.Scrollbar(window_sr_select, orient="vertical", command=tree.yview)
    vsb.place(x=1065, y=10, height=350)
    tree.configure(yscrollcommand=vsb.set)

    # 设置每列宽度和对齐方式
    tree.column("标题", width=200, anchor='center')
    tree.column("金额", width=200, anchor='center')
    tree.column("收入方式", width=200, anchor='center')
    tree.column("时间", width=200, anchor='center')
    tree.column("备注", width=249, anchor='center')

    # 设置每列表头标题文本
    tree.heading("标题", text="标题")
    tree.heading("金额", text="金额")
    tree.heading("收入方式", text="收入方式")
    tree.heading("时间", text="时间")
    tree.heading("备注", text="备注")

    if sr_en4.get() != "" and sr_en41.get() != "" and sr_en1.get() != "":
        db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True',
                             charset='utf8')
        cursor = db.cursor()
        sql = 'select * from income'
        cursor.execute(sql)
        data = cursor.fetchall()  # 获取所有的数据
        try:

            t = 1
            for i in data:
                bt = i[0]
                je = i[1]
                fs = i[2]
                sj = i[3]
                bz = i[4]
                # 只看年份
                if int(i[3][0:4]) > int(sr_en4.get()[0:4]) and int(i[3][0:4]) < int(sr_en41.get()[0:4]) and i[0] == sr_en1.get():
                    # 插入数据
                    tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                # 年份同，比月份
                if int(i[3][0:4]) == int(sr_en4.get()[0:4]) and int(i[3][0:4]) == int(sr_en41.get()[0:4]) and i[0] == sr_en1.get() :
                    if int(i[3][5:7]) > int(sr_en4.get()[5:7]) and int(i[3][5:7]) < int(sr_en41.get()[5:7]):
                        tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                        t = t + 1

                        # 月份同，比天数
                    if int(i[3][5:7]) == int(sr_en4.get()[5:7]) and int(i[3][5:7]) == int(sr_en41.get()[5:7]):
                        if int(i[3][8:10]) >= int(sr_en4.get()[8:10]) and int(i[3][8:10]) <= int(sr_en41.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                        # 年份同，第一个月份同
                    if int(i[3][5:7]) == int(sr_en4.get()[5:7]) and int(i[3][5:7]) < int(sr_en41.get()[5:7]):
                        if int(i[3][8:10]) >= int(sr_en4.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                    if int(i[3][5:7]) > int(sr_en4.get()[5:7]) and int(i[3][5:7]) == int(sr_en41.get()[5:7]):
                        if int(i[3][8:10]) <= int(sr_en41.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                # 第一个年份同
                if int(i[3][0:4]) == int(sr_en4.get()[0:4]) and int(i[3][0:4]) < int(sr_en41.get()[0:4]) and i[0] == sr_en1.get() :
                    # 月份比第一个大的输出
                    if int(i[3][5:7]) > int(sr_en4.get()[5:7]):
                        tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                        t = t + 1

                        # 月份同，比天数
                    if int(i[3][5:7]) == int(sr_en4.get()[5:7]) and int(i[3][5:7]) == int(sr_en41.get()[5:7]):
                        if int(i[3][8:10]) >= int(sr_en4.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                    if int(i[3][5:7]) > int(sr_en4.get()[5:7]) and int(i[3][5:7]) == int(sr_en41.get()[5:7]):
                        if int(i[3][8:10]) <= int(sr_en4.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                if int(i[3][0:4]) > int(sr_en4.get()[0:4]) and int(i[3][0:4]) == int(sr_en41.get()[0:4]) and i[0] == sr_en1.get() :
                    if int(i[3][5:7]) < int(sr_en41.get()[5:7]):
                        tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                        t = t + 1

                        # 月份同，比天数
                    if int(i[3][5:7]) == int(sr_en4.get()[5:7]) and int(i[3][5:7]) == int(sr_en41.get()[5:7]):
                        if int(i[3][8:10]) <= int(sr_en41.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                    if int(i[3][5:7]) > int(sr_en4.get()[5:7]) and int(i[3][5:7]) == int(sr_en41.get()[5:7]):
                        if int(i[3][8:10]) <= int(sr_en41.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

        except:
            print("为空")
        sr_finddata.set("0000-00-00")
        sr_finddata1.set("")
        sr_find.set("")
        db.close()

    if sr_en4.get() == "0000-00-00" and sr_en41.get() == "" and sr_en1.get() != "":
        db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True',
                             charset='utf8')
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        try:
            sql = "SELECT * FROM INCOME "
        except:
            print("null")
        cursor.execute(sql)
    # 获取所有记录列表
        results = cursor.fetchall()
        i = 1
        for row in results:
            bt = row[0]
            je = row[1]
            fs = row[2]
            sj = row[3]
            bz = row[4]
            # 打印结果
            if row[0] == sr_en1.get():
                tree.insert('', i, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
            i = i + 1
        sr_find.set("")
        db.close()

    # 时间
    if sr_en4.get() != "" and sr_en41.get() != "" and sr_en1.get() == "":
        db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True',
                             charset='utf8')
        cursor = db.cursor()
        sql = 'select * from income'
        cursor.execute(sql)
        data = cursor.fetchall()  # 获取所有的数据
        try:
            t = 1
            for i in data:
                bt = i[0]
                je = i[1]
                fs = i[2]
                sj = i[3]
                bz = i[4]
                # 只看年份
                if int(i[3][0:4]) > int(sr_en4.get()[0:4]) and int(i[3][0:4]) < int(sr_en41.get()[0:4]):
                    # 插入数据
                    tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                # 年份同，比月份
                if int(i[3][0:4]) == int(sr_en4.get()[0:4]) and int(i[3][0:4]) == int(sr_en41.get()[0:4]):
                    if int(i[3][5:7]) > int(sr_en4.get()[5:7]) and int(i[3][5:7]) < int(sr_en41.get()[5:7]):
                        tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                        t = t + 1

                        # 月份同，比天数
                    if int(i[3][5:7]) == int(sr_en4.get()[5:7]) and int(i[3][5:7]) == int(sr_en41.get()[5:7]):
                        if int(i[3][8:10]) >= int(sr_en4.get()[8:10]) and int(i[3][8:10]) <= int(sr_en41.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                        # 年份同，第一个月份同
                    if int(i[3][5:7]) == int(sr_en4.get()[5:7]) and int(i[3][5:7]) < int(sr_en41.get()[5:7]):
                        if int(i[3][8:10]) >= int(sr_en4.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                    if int(i[3][5:7]) > int(sr_en4.get()[5:7]) and int(i[3][5:7]) == int(sr_en41.get()[5:7]):
                        if int(i[3][8:10]) <= int(sr_en41.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                # 第一个年份同
                if int(i[3][0:4]) == int(sr_en4.get()[0:4]) and int(i[3][0:4]) < int(sr_en41.get()[0:4]):
                    # 月份比第一个大的输出
                    if int(i[3][5:7]) > int(sr_en4.get()[5:7]) :
                        tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                        t = t + 1

                        # 月份同，比天数
                    if int(i[3][5:7]) == int(sr_en4.get()[5:7]) and int(i[3][5:7]) == int(sr_en41.get()[5:7]):
                        if int(i[3][8:10]) >= int(sr_en4.get()[8:10]) :
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                    if int(i[3][5:7]) > int(sr_en4.get()[5:7]) and int(i[3][5:7]) == int(sr_en41.get()[5:7]):
                        if int(i[3][8:10]) <= int(sr_en4.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1


                if int(i[3][0:4]) > int(sr_en4.get()[0:4]) and int(i[3][0:4]) == int(sr_en41.get()[0:4]):
                    if int(i[3][5:7]) < int(sr_en41.get()[5:7]):
                        tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                        t = t + 1

                        # 月份同，比天数
                    if int(i[3][5:7]) == int(sr_en4.get()[5:7]) and int(i[3][5:7]) == int(sr_en41.get()[5:7]):
                        if int(i[3][8:10]) <= int(sr_en41.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                    if int(i[3][5:7]) > int(sr_en4.get()[5:7]) and int(i[3][5:7]) == int(sr_en41.get()[5:7]) :
                        if int(i[3][8:10]) <= int(sr_en41.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

        except:
            print("为空")
        sr_finddata.set("0000-00-00")
        sr_finddata1.set("")
        db.close()
    window_sr_select.mainloop()



# 更新时间
def sr_time_gx():
    global time1,e4
    curr_t = time.localtime()
    time_s = time.strftime("%Y-%m-%d,%H:%M:%S", curr_t)
    time1 = StringVar(window_shou_ru, value=time_s)
    e4 = Entry(window_shou_ru, textvariable=time1, font=('Arial', 14), width=20, bg="azure", fg="green")
    e4.place(x=130, y=222)




# 更新时间
def zc_time_gx():
    global zc_time,zc_e4
    curr_ti = time.localtime()
    time_st = time.strftime("%Y-%m-%d,%H:%M:%S", curr_ti)
    zc_time = StringVar(window_zhi_chu, value=time_st)
    zc_e4 = Entry(window_zhi_chu, textvariable=zc_time, font=('Arial', 14), width=20, bg="azure", fg="green")
    zc_e4.place(x=130, y=222)


# 收入遍历数据库
def sr_bardata():
    global sum11,sum22,sum33,sum44,sum55,sum66
    db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True',
                           charset='utf8')
    cursor = db.cursor()
    sql = 'select * from income'
    cursor.execute(sql)
    data = cursor.fetchall() # 获取所有的数据
    sum11=0
    sum22=0
    sum33=0
    sum44=0
    sum55=0
    sum66=0
    for i in data:
        if i[0] == '工资':
            str1=i[1]
            sum11=sum11+int(str1)

        elif i[0] =='转账':
            str2 = i[1]
            sum22 = sum22 + int(str2)

        elif i[0] == '红包':
            str3 = i[1]
            sum33 = sum33 + int(str3)

        elif i[0] == '奖金':
            str4 = i[1]
            sum44 = sum44 + int(str4)

        elif i[0] == '股票':
            str5 = i[1]
            sum55 = sum55 + int(str5)

        elif i[0] == '其它':
            str6 = i[1]
            sum66 = sum66 + int(str6)
    db.close()

# 收入柱状图
def draw_bar1(frame,labels,fraces):
    drawPic_f = Figure(figsize=(5, 3), dpi=90,facecolor='ivory')
    drawPic_canvas = FigureCanvasTkAgg(drawPic_f, master=frame)
    drawPic_canvas.draw()
    drawPic_canvas.get_tk_widget().place(x=560,y=25)
    drawPic_a = drawPic_f.add_subplot(111)
    # 解决汉字乱码问题
    matplotlib.rcParams['font.sans-serif'] = ['SimHei']  # 使用指定的汉字字体类型（此处为黑体）
    drawPic_a.bar(labels,fraces,color=["cyan","pink","khaki","lime","magenta","tomato"])
    drawPic_a.set_title('收入总计')
    drawPic_canvas.draw()

def sr_tb():
    sr_bardata()
    labels = ["工资", "转账", "红包", "奖金", "股票", "其它", ]  # 给出要展示的内容（标签）
    fraces = [sum11, sum22, sum33, sum44, sum55, sum66]
    draw_bar1(window_shou_ru, labels, fraces)

# 定义收入窗口方法
def shou_ru():
    global e1, e2, e3, e4, e5, message1, biao_ti, price, way, memo, time1,tree,columns,\
        sr_en1,sr_find,window_shou_ru,sr_en4,sr_en41,sr_en42,sr_finddata,sr_finddata1
    window_shou_ru = Tk()
    window_shou_ru.title("收入管理")  # 收入窗口

    # 固定窗口位置
    width = 1090
    height = 900
    screenwidth = window_shou_ru.winfo_screenwidth()
    screenheight = window_shou_ru.winfo_screenheight()
    alignstr = '%dx%d+%d+%d' % (width, height, (screenwidth - width) / 2, (screenheight - height) / 5)
    window_shou_ru.geometry(alignstr)

    # 设置窗口大小不可改变
    window_shou_ru.resizable(width=False, height=False)
    # 设置窗口颜色
    window_shou_ru.configure(background='ivory')

    # 柱状图
    b_tb = Button(
        window_shou_ru,
        text="收入图表",  # 文本
        bg='limegreen',  # 背景颜色
        fg='snow',  # 文本颜色
        relief='raised',  # 边框的3D样式
        bd=2,  # 边框的大小
        height=1,  # 高度
        width=7,  # 宽度
        padx=1,  # 内间距，字体与边框的X距离
        pady=1,  # 内间距，字体与边框的Y距离
        cursor='arrow',
        font=('Yu Gothic Medium', 10),  # 字体
        command=sr_tb,  # 点击事件
    )
    b_tb.place(x=480, y=25)


    # 设置收入管理窗口标签
    Label(window_shou_ru, text="收入管理", font=('Arial', 22), fg="darkorange").place(x=20, y=10)
    Label(window_shou_ru, text="标题: ", font=('Arial', 15), ).place(x=20, y=80)
    Label(window_shou_ru, text="金额: ", font=('Arial', 15)).place(x=20, y=120)
    Label(window_shou_ru, text="收入方式:", font=('Arial', 15)).place(x=20, y=170)
    Label(window_shou_ru, text="时间: ", font=('Arial', 15)).place(x=20, y=220)
    Label(window_shou_ru, text="备注: ", font=('Arial', 15)).place(x=20, y=270)


    biao_ti = StringVar(window_shou_ru,value="")  # 窗体自带的文本，新建一个值
    e1 = ttk.Combobox(window_shou_ru, width=29, textvariable=biao_ti)  # 初始化
    e1["values"] = ("工资", "转账", "红包", "奖金", "股票","其它",)
    e1.current(0)  # 选择第一个
    e1.bind("<<ComboboxSelected>>")  # 绑定事件,(下拉列表框被选中时，绑定go()函数)
    e1.place(x=130, y=82)


    price = StringVar(window_shou_ru, value="")
    e2 = Entry(window_shou_ru, textvariable=price, font=('Arial', 14), width=20, fg="green")
    e2.place(x=130, y=122)

    way = StringVar(window_shou_ru, value="")
    e3 = Entry(window_shou_ru, textvariable=way, font=('Arial', 14), width=20,fg="green")
    e3.place(x=130, y=172)

    curr_time = time.localtime()
    time_str = time.strftime("%Y-%m-%d,%H:%M:%S", curr_time);

    time1 = StringVar(window_shou_ru, value=time_str)
    e4 = Entry(window_shou_ru, textvariable=time1, font=('Arial', 14), width=20,  fg="green")
    e4.place(x=130, y=222)

    memo = StringVar(window_shou_ru, value="")
    e5 = Entry(window_shou_ru, textvariable=memo, font=('Arial', 14), width=20,  fg="green")
    e5.place(x=130, y=272)

    # 收入框保存数据按钮
    b1 = Button(
        window_shou_ru,
        text="记账",  # 文本
        bg='limegreen',  # 背景颜色
        fg='snow',  # 文本颜色
        relief='raised',  # 边框的3D样式
        bd=2,  # 边框的大小
        height=1,  # 高度
        width=4,  # 宽度
        padx=1,  # 内间距，字体与边框的X距离
        pady=1,  # 内间距，字体与边框的Y距离
        cursor='arrow',
        font=('Yu Gothic Medium', 10),  # 字体
        command=sr_add,  # 点击事件
    )
    b1.place(x=310, y=312)

    #更新时间
    btime_gx = Button(
        window_shou_ru,
        text="刷新",  # 文本
        bg='azure',  # 背景颜色
        fg='green',  # 文本颜色
        relief='raised',  # 边框的3D样式
        height=1,  # 高度
        width=3,  # 宽度
        cursor='arrow',
        font=('Arial', 9),  # 字体
        command=sr_time_gx,  # 点击事件
    )
    btime_gx.place(x=355, y=222)
    # 消息提示框
    message1 = Label(window_shou_ru, text='', font=('Arial', 18), width=20, bg="ivory", fg="red")
    message1.place(x=360, y=312)

    # 表格框
    style = ttk.Style(window_shou_ru)
    style.theme_use("clam")
    style.configure("Treeview", background="black",
                    fieldbackground="ivory", foreground="ivory")
    columns = ("标题", "金额", "收入方式", "时间", "备注")
    tree = ttk.Treeview(window_shou_ru, show="headings",
                        columns = ("标题", "金额", "收入方式", "时间", "备注"), height=23,
                        selectmode="browse")
    tree.place(x=10, y=350)

    # 设置滚动条
    vsb = ttk.Scrollbar(window_shou_ru, orient="vertical", command=tree.yview)
    vsb.place(x=1065, y=350, height=482)
    tree.configure(yscrollcommand=vsb.set)

    # 设置每列宽度和对齐方式
    tree.column("标题", width=200, anchor='center')
    tree.column("金额", width=200, anchor='center')
    tree.column("收入方式", width=161, anchor='center')
    tree.column("时间", width=249, anchor='center')
    tree.column("备注", width=249, anchor='center')

    # 设置每列表头标题文本
    tree.heading("标题", text="标题")
    tree.heading("金额", text="金额")
    tree.heading("收入方式", text="支付方式")
    tree.heading("时间", text="时间")
    tree.heading("备注", text="备注")

    btn1 = Button(
        window_shou_ru,
        text="刪除",  # 文本
        bg='azure',  # 背景颜色
        fg='black',  # 文本颜色
        relief='raised',  # 边框的3D样式
        bd=5,  # 边框的大小
        height=1,  # 高度
        width=10,  # 宽度
        padx=1,  # 内间距，字体与边框的X距离
        pady=1,  # 内间距，字体与边框的Y距离
        cursor='arrow',
        font=('Arial', 12),  # 字体
        command=treeviewClick,  # 点击事件
    )
    btn1.place(x=610, y=850)

    db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True',
                         charset='utf8')
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    sql = "SELECT * FROM INCOME"
    cursor.execute(sql)
    # 获取所有记录列表
    results = cursor.fetchall()
    i=1
    for row in results:
        fname = row[0]
        lname = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
        # 打印结果
        tree.insert('', i, values="%s %s %s %s %s" % (fname, lname, age, sex, income))
        i=i+1
    db.close()

    # 编辑
    Label(window_shou_ru, text="编辑输入:", font=('Arial', 16), foreground="ivory", fg='green').place(x=750, y=855)
    # 查询
    Label(window_shou_ru, text="查询:", font=('Arial', 16), foreground="ivory", fg='green').place(x=10, y=855)
    Label(window_shou_ru, text="标题:", font=('Arial', 10), foreground="ivory", fg='green').place(x=70, y=860)
    Label(window_shou_ru, text="时间:", font=('Arial', 10), foreground="ivory", fg='green').place(x=220, y=860)
    Label(window_shou_ru, text="—", font=('Arial', 10), foreground="ivory", fg='green').place(x=385, y=859)
    sr_find = StringVar(window_shou_ru, value="")
    sr_en1 = Entry(window_shou_ru, textvariable=sr_find, font=('Arial', 16), width=8, fg="pink", bg="azure")
    sr_en1.place(x=110, y=855)
    #时间查询
    sr_finddata = StringVar(window_shou_ru, value="0000-00-00")
    sr_en4 = Entry(window_shou_ru, textvariable=sr_finddata, font=('Arial', 16), width=10, fg="pink", bg="azure")
    sr_en4.place(x=260, y=855)

    sr_finddata1 = StringVar(window_shou_ru, value="")
    sr_en41 = Entry(window_shou_ru, textvariable=sr_finddata1, font=('Arial', 16), width=10, fg="pink", bg="azure")
    sr_en41.place(x=405, y=855)

    btn4 = Button(
        window_shou_ru,
        text="Yes",  # 文本
        bg='azure',  # 背景颜色
        fg='green',  # 文本颜色
        relief='raised',  # 边框的3D样式
        bd=3,  # 边框的大小
        height=1,  # 高度
        width=3,  # 宽度
        padx=1,  # 内间距，字体与边框的X距离
        pady=1,  # 内间距，字体与边框的Y距离
        cursor='arrow',
        font=('Arial', 9),  # 字体
        command=sr_select,  # 点击事件
    )
    btn4.place(x=540, y=855)

    # 绑定函数，使表头可排序
    for col in columns:
        tree.heading(col, text=col, command=lambda _col=col: sr_treeview_sort_column(tree, _col, False))


    # 更新数据库
    def sr_update():
        db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True',
                                 charset='utf8')
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        if cn == 1:
            sql = "UPDATE INCOME SET SR_BT ='%s' WHERE SR_SJ = '%s'" % (entryedit.get(0.0, "end"), item_text[3])
        elif cn == 2:
            sql = "UPDATE INCOME SET SR_JE ='%s' WHERE SR_SJ= '%s'" % (entryedit.get(0.0, "end"), item_text[3])
        elif cn == 3:
            sql = "UPDATE INCOME SET SR_FS ='%s' WHERE SR_SJ = '%s'" % (entryedit.get(0.0, "end"), item_text[3])
        elif cn == 4:
            sql = "UPDATE INCOME SET SR_SJ ='%s' WHERE SR_SJ = '%s'" % (entryedit.get(0.0, "end"), item_text[3])
        elif cn == 5:
            sql = "UPDATE INCOME SET SR_BZ ='%s' WHERE SR_SJ = '%s'" % (entryedit.get(0.0, "end"), item_text[3])
        cursor.execute(sql)
        db.commit()
        db.close()

    def set_cell_value(event):  # 双击进入编辑状态
        global item_text,cn,entryedit
        for item in tree.selection():
            item_text = tree.item(item,"values")
        column = tree.identify_column(event.x)  # 列
        cn = int(str(column).replace('#',''))
        entryedit = Text(window_shou_ru, font=('Arial', 16), width=11, height=1, fg="red", bg="azure")
        entryedit.place(x=850, y=856)

        # ok按钮
        def saveedit():
            tree.set(item, column=column, value=entryedit.get(0.0, "end"))
            sr_update()
            entryedit.destroy()
            okb.destroy()
        okb = Button(
            window_shou_ru,
            text="OK",  # 文本
            bg='azure',  # 背景颜色
            fg='deeppink',  # 文本颜色
            relief='raised',  # 边框的3D样式
            bd=3,  # 边框的大小
            height=1,  # 高度
            width=3,  # 宽度
            padx=1,  # 内间距，字体与边框的X距离
            pady=1,  # 内间距，字体与边框的Y距离
            cursor='arrow',
            font=('Arial', 10),  # 字体
            command=saveedit,  # 点击事件
        )
        okb.place(x=1000, y=855)
    tree.bind('<Double-1>', set_cell_value)
    window_shou_ru.mainloop()




# 定义支出删除
def zc_delete():
    # 打开数据库连接
    db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True', charset='utf8')
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    # 删除数据对应的数据
    cursor.execute("DELETE FROM PAY WHERE ZC_SJ ='" + str_sj + "'")
    db.commit()
    db.close()


# 支出单击事件
def treeviewClick2():#单击

    global treeviewClick2,str_sj
    for item in tree1.selection():
        item_textt = tree1.item(item, "values")
        tree1.delete(item)
        str_sj = item_textt[3]# 输出所选行的第4列的值-时间
        zc_delete()

# 定义支出排序
def zc_treeview_sort_column(tv, col, reverse):  # Treeview、列名、排列方式
    l = [(tv.set(k, col), k) for k in tv.get_children('')]
    l.sort(reverse=reverse)  # 排序方式
    for index, (val, k) in enumerate(l):  # 根据排序后索引移动
        tv.move(k, '', index)
    tv.heading(col, command=lambda: zc_treeview_sort_column(tv, col, not reverse))  # 重写标题，使之成为再点倒序的标题

#  支出查询
def zc_select():
    window_zc_select = Tk()
    window_zc_select.title("支出查询")  # 收入窗口

    # 固定窗口位置
    width = 1080
    height = 455
    screenwidth = window_zc_select.winfo_screenwidth()
    screenheight = window_zc_select.winfo_screenheight()
    alignstr = '%dx%d+%d+%d' % (width, height, (screenwidth - width) / 2, (screenheight - height) / 1.5)
    window_zc_select.geometry(alignstr)

    # 设置窗口大小不可改变
    window_zc_select.resizable(width=False, height=False)
    # 设置窗口颜色
    window_zc_select.configure(background='azure')

    # 表格框
    style = ttk.Style(window_zc_select)
    style.theme_use("clam")
    style.configure("Treeview", background="black",
                    fieldbackground="azure", foreground="azure")
    tree = ttk.Treeview(window_zc_select, show="headings",
                        columns=("标题", "金额", "支出方式", "时间", "备注"), height=23,
                        selectmode="browse")
    tree.place(x=10, y=10)

    # 设置滚动条
    vsb = ttk.Scrollbar(window_zc_select, orient="vertical", command=tree.yview)
    vsb.place(x=1065, y=10, height=350)
    tree.configure(yscrollcommand=vsb.set)

    # 设置每列宽度和对齐方式
    tree.column("标题", width=200, anchor='center')
    tree.column("金额", width=200, anchor='center')
    tree.column("支出方式", width=200, anchor='center')
    tree.column("时间", width=200, anchor='center')
    tree.column("备注", width=249, anchor='center')

    # 设置每列表头标题文本
    tree.heading("标题", text="标题")
    tree.heading("金额", text="金额")
    tree.heading("支出方式", text="支出方式")
    tree.heading("时间", text="时间")
    tree.heading("备注", text="备注")

    if zc_en4.get() != "" and zc_en41.get() != "" and zc_en1.get() != "":
        db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True',
                             charset='utf8')
        cursor = db.cursor()
        sql = 'select * from pay'
        cursor.execute(sql)
        data = cursor.fetchall()  # 获取所有的数据
        try:
            t = 1
            for i in data:
                bt = i[0]
                je = i[1]
                fs = i[2]
                sj = i[3]
                bz = i[4]
                # 只看年份
                if int(i[3][0:4]) > int(zc_en4.get()[0:4]) and int(i[3][0:4]) < int(zc_en41.get()[0:4]) and i[0]== zc_en1.get():
                    # 插入数据
                    tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                # 年份同，比月份
                if int(i[3][0:4]) == int(zc_en4.get()[0:4]) and int(i[3][0:4]) == int(zc_en41.get()[0:4]) and i[0]== zc_en1.get() :
                    if int(i[3][5:7]) > int(zc_en4.get()[5:7]) and int(i[3][5:7]) < int(zc_en41.get()[5:7]):
                        tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                        t = t + 1

                        # 月份同，比天数
                    if int(i[3][5:7]) == int(zc_en4.get()[5:7]) and int(i[3][5:7]) == int(zc_en41.get()[5:7]):
                        if int(i[3][8:10]) >= int(zc_en4.get()[8:10]) and int(i[3][8:10]) <= int(zc_en41.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                        # 年份同，第一个月份同
                    if int(i[3][5:7]) == int(zc_en4.get()[5:7]) and int(i[3][5:7]) < int(zc_en41.get()[5:7]):
                        if int(i[3][8:10]) >= int(zc_en4.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                    if int(i[3][5:7]) > int(zc_en4.get()[5:7]) and int(i[3][5:7]) == int(zc_en41.get()[5:7]):
                        if int(i[3][8:10]) <= int(zc_en41.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                # 第一个年份同
                if int(i[3][0:4]) == int(zc_en4.get()[0:4]) and int(i[3][0:4]) < int(zc_en41.get()[0:4]) and i[0]== zc_en1.get():
                    # 月份比第一个大的输出
                    if int(i[3][5:7]) > int(zc_en4.get()[5:7]):
                        tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                        t = t + 1

                        # 月份同，比天数
                    if int(i[3][5:7]) == int(zc_en4.get()[5:7]) and int(i[3][5:7]) == int(zc_en41.get()[5:7]):
                        if int(i[3][8:10]) >= int(zc_en4.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                    if int(i[3][5:7]) > int(zc_en4.get()[5:7]) and int(i[3][5:7]) == int(zc_en41.get()[5:7]):
                        if int(i[3][8:10]) <= int(zc_en4.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                if int(i[3][0:4]) > int(zc_en4.get()[0:4]) and int(i[3][0:4]) == int(zc_en41.get()[0:4]) and i[0]== zc_en1.get():
                    if int(i[3][5:7]) < int(zc_en41.get()[5:7]):
                        tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                        t = t + 1

                        # 月份同，比天数
                    if int(i[3][5:7]) == int(zc_en4.get()[5:7]) and int(i[3][5:7]) == int(zc_en41.get()[5:7]):
                        if int(i[3][8:10]) <= int(zc_en41.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                    if int(i[3][5:7]) > int(zc_en4.get()[5:7]) and int(i[3][5:7]) == int(zc_en41.get()[5:7]):
                        if int(i[3][8:10]) <= int(zc_en41.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

        except:
            print("为空")
        zc_finddata.set("0000-00-00")
        zc_finddata1.set("")
        zc_find.set("")
        db.close()

    if zc_en4.get() == "0000-00-00" and zc_en41.get() == "" and zc_en1.get() != "":
        db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True',
                             charset='utf8')
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        try:
            sql = "SELECT * FROM pay "
        except:
            print("null")
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        i = 1
        for row in results:
            bt = row[0]
            je = row[1]
            fs = row[2]
            sj = row[3]
            bz = row[4]
            # 打印结果
            if row[0] == zc_en1.get():
                tree.insert('', i, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
            i = i + 1
        zc_find.set("")
        db.close()

    # 时间
    if zc_en4.get() != "" and zc_en41.get() != "" and zc_en1.get() == "":
        db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True',
                             charset='utf8')
        cursor = db.cursor()
        sql = 'select * from pay'
        cursor.execute(sql)
        data = cursor.fetchall()  # 获取所有的数据
        try:
            t = 1
            for i in data:
                bt = i[0]
                je = i[1]
                fs = i[2]
                sj = i[3]
                bz = i[4]
                # 只看年份
                if int(i[3][0:4]) > int(zc_en4.get()[0:4]) and int(i[3][0:4]) < int(zc_en41.get()[0:4]):
                    # 插入数据
                    tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                # 年份同，比月份
                if int(i[3][0:4]) == int(zc_en4.get()[0:4]) and int(i[3][0:4]) == int(zc_en41.get()[0:4]):
                    if int(i[3][5:7]) > int(zc_en4.get()[5:7]) and int(i[3][5:7]) < int(zc_en41.get()[5:7]):
                        tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                        t = t + 1

                        # 月份同，比天数
                    if int(i[3][5:7]) == int(zc_en4.get()[5:7]) and int(i[3][5:7]) == int(zc_en41.get()[5:7]):
                        if int(i[3][8:10]) >= int(zc_en4.get()[8:10]) and int(i[3][8:10]) <= int(zc_en41.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                        # 年份同，第一个月份同
                    if int(i[3][5:7]) == int(zc_en4.get()[5:7]) and int(i[3][5:7]) < int(zc_en41.get()[5:7]):
                        if int(i[3][8:10]) >= int(zc_en4.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                    if int(i[3][5:7]) > int(zc_en4.get()[5:7]) and int(i[3][5:7]) == int(zc_en41.get()[5:7]):
                        if int(i[3][8:10]) <= int(zc_en41.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                # 第一个年份同
                if int(i[3][0:4]) == int(zc_en4.get()[0:4]) and int(i[3][0:4]) < int(zc_en41.get()[0:4]):
                    # 月份比第一个大的输出
                    if int(i[3][5:7]) > int(zc_en4.get()[5:7]):
                        tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                        t = t + 1

                        # 月份同，比天数
                    if int(i[3][5:7]) == int(zc_en4.get()[5:7]) and int(i[3][5:7]) == int(zc_en41.get()[5:7]):
                        if int(i[3][8:10]) >= int(zc_en4.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                    if int(i[3][5:7]) > int(zc_en4.get()[5:7]) and int(i[3][5:7]) == int(zc_en41.get()[5:7]):
                        if int(i[3][8:10]) <= int(zc_en4.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                if int(i[3][0:4]) > int(zc_en4.get()[0:4]) and int(i[3][0:4]) == int(zc_en41.get()[0:4]):
                    if int(i[3][5:7]) < int(zc_en41.get()[5:7]):
                        tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                        t = t + 1

                        # 月份同，比天数
                    if int(i[3][5:7]) == int(zc_en4.get()[5:7]) and int(i[3][5:7]) == int(zc_en41.get()[5:7]):
                        if int(i[3][8:10]) <= int(zc_en41.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

                    if int(i[3][5:7]) > int(zc_en4.get()[5:7]) and int(i[3][5:7]) == int(zc_en41.get()[5:7]):
                        if int(i[3][8:10]) <= int(zc_en41.get()[8:10]):
                            tree.insert('', t, values="%s %s %s %s %s" % (bt, je, fs, sj, bz))
                            t = t + 1

        except:
            print("为空")
        zc_finddata.set("0000-00-00")
        zc_finddata1.set("")
        db.close()
    window_zc_select.mainloop()


# 支出数据库遍历数据
def zc_bardata():
    global sum1,sum2,sum3,sum4,sum5,sum6
    db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True',
                           charset='utf8')
    cursor = db.cursor()
    sql = 'select * from pay'
    cursor.execute(sql)
    data = cursor.fetchall() # 获取所有的数据
    sum1=0
    sum2=0
    sum3=0
    sum4=0
    sum5=0
    sum6=0
    for i in data:
        if i[0] == '日用百货':
            str1=i[1]

            sum1=sum1+int(str1)
        elif i[0] =='数码电器':
            str2 = i[1]

            sum2 = sum2 + int(str2)
        elif i[0] == '文化休闲':
            str3 = i[1]

            sum3 = sum3 + int(str3)
        elif i[0] == '服饰装扮':
            str4 = i[1]

            sum4 = sum4 + int(str4)
        elif i[0] == '教育培训':
            str5 = i[1]

            sum5 = sum5 + int(str5)
        elif i[0] == '其它':
            str6 = i[1]
            sum6 = sum6 + int(str6)

    db.close()

#  绘图
def draw_bar(frame,labels,fraces):
    drawPic_f = Figure(figsize=(5, 3), dpi=95,facecolor='ivory')

    drawPic_canvas = FigureCanvasTkAgg(drawPic_f, master=frame)
    drawPic_canvas.draw()
    drawPic_canvas.get_tk_widget().place(x=560,y=25)
    drawPic_a = drawPic_f.add_subplot(111)
    # 解决汉字乱码问题
    matplotlib.rcParams['font.sans-serif'] = ['SimHei']  # 使用指定的汉字字体类型（此处为黑体）
    drawPic_a.bar(labels,fraces,color=["cyan","pink","khaki","lime","magenta","tomato"])
    drawPic_a.set_title('支出总计')
    drawPic_canvas.draw()


def zc_tb():
    zc_bardata()
    labels = ['日用百货', '数码电器', '文化休闲', '服饰装扮', '教育培训', '其它']  # 给出要展示的内容（标签）
    fraces = [sum1, sum2, sum3, sum4, sum5, sum6]
    draw_bar(window_zhi_chu, labels, fraces)

# 定义支出窗口方法
def zhi_chu():
    global zc_e1,zc_e2,zc_e3,zc_e4,zc_e5,zc_biao_ti,zc_price, zc_memo,zc_time,zc_way,message2,\
    tree1,columns,zc_en1,zc_find,window_zhi_chu,zc_en4,zc_en41,zc_finddata,zc_finddata1
    window_zhi_chu = Tk()
    window_zhi_chu.title("支出管理")  # 收入窗口

    # 固定窗口位置
    width = 1090
    height = 900
    screenwidth = window_zhi_chu.winfo_screenwidth()
    screenheight = window_zhi_chu.winfo_screenheight()
    alignstr = '%dx%d+%d+%d' % (width, height, (screenwidth - width) / 2, (screenheight - height) / 5)
    window_zhi_chu.geometry(alignstr)

    # 设置窗口大小不可改变
    window_zhi_chu.resizable(width=False, height=False)
    # 设置窗口颜色
    window_zhi_chu.configure(background='ivory')

    # 绘制柱状图
    b_tb = Button(
        window_zhi_chu,
        text="支出图表",  # 文本
        bg='limegreen',  # 背景颜色
        fg='snow',  # 文本颜色
        relief='raised',  # 边框的3D样式
        bd=2,  # 边框的大小
        height=1,  # 高度
        width=7,  # 宽度
        padx=1,  # 内间距，字体与边框的X距离
        pady=1,  # 内间距，字体与边框的Y距离
        cursor='arrow',
        font=('Yu Gothic Medium', 10),  # 字体
        command=zc_tb,  # 点击事件
    )
    b_tb.place(x=480, y=25)

    # 设置收入管理窗口标签
    Label(window_zhi_chu, text="支出管理", font=('Arial', 22), fg="darkorange").place(x=20, y=10)
    Label(window_zhi_chu, text="标题: ", font=('Arial', 15), ).place(x=20, y=80)
    Label(window_zhi_chu, text="金额: ", font=('Arial', 15)).place(x=20, y=120)
    Label(window_zhi_chu, text="支出方式:", font=('Arial', 15)).place(x=20, y=170)
    Label(window_zhi_chu, text="时间: ", font=('Arial', 15)).place(x=20, y=220)
    Label(window_zhi_chu, text="备注: ", font=('Arial', 15)).place(x=20, y=270)


    zc_biao_ti = StringVar(window_zhi_chu, value="")  # 窗体自带的文本，新建一个值
    zc_e1 = ttk.Combobox(window_zhi_chu,width=29,textvariable=zc_biao_ti)  # 初始化
    zc_e1["values"] = ("日用百货", "数码电器", "文化休闲", "服饰装扮", "教育培训", "其它",)
    zc_e1.current(0)  # 选择第一个
    zc_e1.bind("<<ComboboxSelected>>")  # 绑定事件,(下拉列表框被选中时，绑定go()函数)
    zc_e1.place(x=130, y=82)

    zc_price = StringVar(window_zhi_chu, value="")
    zc_e2 = Entry(window_zhi_chu, textvariable=zc_price, font=('Arial', 14), width=20,  fg="green")
    zc_e2.place(x=130, y=122)

    zc_way = StringVar(window_zhi_chu, value="")
    zc_e3 = Entry(window_zhi_chu, textvariable=zc_way, font=('Arial', 14), width=20, fg="green")
    zc_e3.place(x=130, y=172)

    curr_time = time.localtime()
    time_str = time.strftime("%Y-%m-%d,%H:%M:%S", curr_time);

    zc_time = StringVar(window_zhi_chu, value=time_str)
    zc_e4 = Entry(window_zhi_chu, textvariable=zc_time, font=('Arial', 14), width=20,  fg="green")
    zc_e4.place(x=130, y=222)

    zc_memo = StringVar(window_zhi_chu, value="")
    zc_e5 = Entry(window_zhi_chu, textvariable=zc_memo, font=('Arial', 14), width=20,  fg="green")
    zc_e5.place(x=130, y=272)


    # 支出框保存数据按钮
    b1 = Button(
        window_zhi_chu,
        text="记账",  # 文本
        bg='limegreen',  # 背景颜色
        fg='snow',  # 文本颜色
        relief='raised',  # 边框的3D样式
        bd=2,  # 边框的大小
        height=1,  # 高度
        width=4,  # 宽度
        padx=1,  # 内间距，字体与边框的X距离
        pady=1,  # 内间距，字体与边框的Y距离
        cursor='arrow',
        font=('Yu Gothic Medium', 10),  # 字体
        command=zc_add,  # 点击事件
    )
    b1.place(x=310, y=312)

    #更新时间
    btime_gx = Button(
        window_zhi_chu,
        text="刷新",  # 文本
        bg='azure',  # 背景颜色
        fg='green',  # 文本颜色
        relief='raised',  # 边框的3D样式
        height=1,  # 高度
        width=3,  # 宽度
        cursor='arrow',
        font=('Arial', 9),  # 字体
        command=zc_time_gx,  # 点击事件
    )
    btime_gx.place(x=355, y=222)
    # 消息提示框
    message2 = Label(window_zhi_chu, text='', font=('Arial', 18), width=20, bg="ivory", fg="red")
    message2.place(x=360, y=312)

    # 表格框
    style = ttk.Style(window_zhi_chu)
    style.theme_use("clam")
    style.configure("Treeview", background="black",
                    fieldbackground="ivory", foreground="ivory")
    columns = ("标题", "金额", "支出方式", "时间", "备注")
    tree1 = ttk.Treeview(window_zhi_chu, show="headings",columns = ("标题", "金额", "支出方式", "时间", "备注"), height=23,selectmode="browse")
    tree1.place(x=10, y=350)

    # 设置滚动条
    vsb = ttk.Scrollbar(window_zhi_chu, orient="vertical", command=tree1.yview)
    vsb.place(x=1065, y=350, height=482)
    tree1.configure(yscrollcommand=vsb.set)

    # 设置每列宽度和对齐方式
    tree1.column("标题", width=200, anchor='center')
    tree1.column("金额", width=200, anchor='center')
    tree1.column("支出方式", width=161, anchor='center')
    tree1.column("时间", width=249, anchor='center')
    tree1.column("备注", width=249, anchor='center')

    # 设置每列表头标题文本
    tree1.heading("标题", text="标题")
    tree1.heading("金额", text="金额")
    tree1.heading("支出方式", text="支出方式")
    tree1.heading("时间", text="时间")
    tree1.heading("备注", text="备注")

    btn1 = Button(
        window_zhi_chu,
        text="刪除",  # 文本
        bg='azure',  # 背景颜色
        fg='black',  # 文本颜色
        relief='raised',  # 边框的3D样式
        bd=5,  # 边框的大小
        height=1,  # 高度
        width=10,  # 宽度
        padx=1,  # 内间距，字体与边框的X距离
        pady=1,  # 内间距，字体与边框的Y距离
        cursor='arrow',
        font=('Arial', 12),  # 字体
        command=treeviewClick2,  # 点击事件
    )
    btn1.place(x=610, y=850)

    # 同步数据
    db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True',
                         charset='utf8')
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    sql = "SELECT * FROM PAY"
    cursor.execute(sql)
    # 获取所有记录列表
    results = cursor.fetchall()
    i=1
    for row in results:
        fname = row[0]
        lname = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
        # 打印结果
        tree1.insert('', i, values="%s %s %s %s %s" % (fname, lname, age, sex, income))
        i=i+1
    db.close()


    # 编辑
    Label(window_zhi_chu, text="编辑输入:", font=('Arial', 16), foreground="ivory", fg='green').place(x=750, y=855)
    # 查询
    Label(window_zhi_chu, text="查询:", font=('Arial', 16), foreground="ivory", fg='green').place(x=10, y=855)
    Label(window_zhi_chu, text="标题:", font=('Arial', 10), foreground="ivory", fg='green').place(x=70, y=860)
    Label(window_zhi_chu, text="时间:", font=('Arial', 10), foreground="ivory", fg='green').place(x=220, y=860)
    Label(window_zhi_chu, text="—", font=('Arial', 10), foreground="ivory", fg='green').place(x=385, y=859)
    zc_find = StringVar(window_zhi_chu, value="")
    zc_en1 = Entry(window_zhi_chu, textvariable=zc_find, font=('Arial', 16), width=8, fg="pink", bg="azure")
    zc_en1.place(x=110, y=855)
    #
    zc_finddata = StringVar(window_zhi_chu, value="0000-00-00")
    zc_en4 = Entry(window_zhi_chu, textvariable=zc_finddata, font=('Arial', 16), width=10, fg="pink", bg="azure")
    zc_en4.place(x=260, y=855)

    zc_finddata1 = StringVar(window_zhi_chu, value="")
    zc_en41 = Entry(window_zhi_chu, textvariable=zc_finddata1, font=('Arial', 16), width=10, fg="pink", bg="azure")
    zc_en41.place(x=405, y=855)

    btn4 = Button(
        window_zhi_chu,
        text="Yes",  # 文本
        bg='azure',  # 背景颜色
        fg='green',  # 文本颜色
        relief='raised',  # 边框的3D样式
        bd=3,  # 边框的大小
        height=1,  # 高度
        width=3,  # 宽度
        padx=1,  # 内间距，字体与边框的X距离
        pady=1,  # 内间距，字体与边框的Y距离
        cursor='arrow',
        font=('Arial', 9),  # 字体
        command=zc_select,  # 点击事件
    )
    btn4.place(x=540, y=855)

    for col in columns:  # 绑定函数，使表头可排序
        tree1.heading(col, text=col, command=lambda _col=col: zc_treeview_sort_column(tree1, _col, False))

    # 表格滑动条
    # for i in range(100):  # 写入数据
    #     tree1.insert('', i, values="1")

    # 更新数据库
    def zc_update():
        db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True',
                                 charset='utf8')
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        if cn1 == 1:
            sql = "UPDATE PAY SET ZC_BT ='%s' WHERE ZC_SJ = '%s'" % (entryedit1.get(0.0, "end"), item_text1[3])
        elif cn1 == 2:
            sql = "UPDATE PAY SET ZC_JE ='%s' WHERE ZC_SJ= '%s'" % (entryedit1.get(0.0, "end"), item_text1[3])
        elif cn1 == 3:
            sql = "UPDATE PAY SET ZC_FS ='%s' WHERE ZC_SJ = '%s'" % (entryedit1.get(0.0, "end"), item_text1[3])
        elif cn1 == 4:
            sql = "UPDATE PAY SET ZC_SJ ='%s' WHERE ZC_SJ = '%s'" % (entryedit1.get(0.0, "end"), item_text1[3])
        elif cn1 == 5:
            sql = "UPDATE PAY SET ZC_BZ ='%s' WHERE ZC_SJ = '%s'" % (entryedit1.get(0.0, "end"), item_text1[3])
        cursor.execute(sql)
        db.commit()
        db.close()

    def set_cell_value1(event):  # 双击进入编辑状态
        global item_text1,cn1,entryedit1
        for item in tree1.selection():
            item_text1 = tree1.item(item, "values")
        column = tree1.identify_column(event.x)  # 列
        cn1 = int(str(column).replace('#', ''))
        entryedit1 = Text(window_zhi_chu, font=('Arial', 16), width=11, height=1, fg="red", bg="azure")
        entryedit1.place(x=850, y=856)
        # ok按钮
        def saveedit1():
            tree1.set(item, column=column, value=entryedit1.get(0.0, "end"))
            zc_update()
            entryedit1.destroy()
            okb1.destroy()
        okb1 = Button(
            window_zhi_chu,
            text="OK",  # 文本
            bg='azure',  # 背景颜色
            fg='deeppink',  # 文本颜色
            relief='raised',  # 边框的3D样式
            bd=3,  # 边框的大小
            height=1,  # 高度
            width=3,  # 宽度
            padx=1,  # 内间距，字体与边框的X距离
            pady=1,  # 内间距，字体与边框的Y距离
            cursor='arrow',
            font=('Arial', 10),  # 字体
            command=saveedit1,  # 点击事件
        )
        okb1.place(x=1000, y=855)

    tree1.bind('<Double-1>', set_cell_value1)

    window_zhi_chu.mainloop()




# 数据库操作
# def sjk():
#
#     db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True', charset='utf8')
#     # 使用cursor()方法获取操作游标
#     cursor = db.cursor()
#
#     # 创建数据表SQL语句
#     sql = """CREATE TABLE EMPLOYEE (
#                      FIRST_NAME  CHAR(20) NOT NULL,
#                      LAST_NAME  CHAR(20),
#                      AGE INT,
#                      SEX CHAR(1),
#                      INCOME FLOAT )"""
#     cursor.execute(sql)
#
#     # 关闭数据库连接
#     db.close()



# 收入数据完整性验证
def sr_validation():
    return len(e1.get()) != 0 and len(e2.get()) != 0 and len(e3.get()) != 0

def sr_add():
    # 打开数据库连接
    db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True', charset='utf8')
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    if sr_validation():
        # sql插入语句
        # 获取系统当前日期
        curr_time = time.localtime()
        time_str = time.strftime("%Y-%m-%d,%H:%M:%S", curr_time);
        if len(e4.get()) != 0:
            sql1 = (e1.get(), e2.get(), e3.get(), e4.get(), e5.get())
        else:
            sql1 = (e1.get(), e2.get(), e3.get(), time_str, e5.get())

        cursor.execute("INSERT INTO INCOME(SR_BT ,SR_JE,SR_FS,SR_SJ,SR_BZ) VALUES( %s, %s, %s, %s,%s)", sql1)
        message1['text'] = "添加成功"
        tree.insert('', "end", values=(str(e1.get()),str(e2.get()),str(e3.get()),str(e4.get()),str(e5.get())))
        # 添加成功后清空文本框
        biao_ti.set("")
        price.set("")
        way.set("")
        memo.set("")
    else:
        message1['text'] = "标题,金额,方式,不能为空"
        # 提交到数据库执
    db.commit()
    db.close()


# 支出数据库操作
# 支出数据完整性验证
def zc_validation():
    return len(zc_e1.get()) != 0 and len(zc_e2.get()) != 0 and len(zc_e3.get()) != 0

# 支出数据库操作
def zc_add():
    # 打开数据库连接
    db = pymysql.connect(host="localhost", user="root", password="admin", db="acc", use_unicode='True', charset='utf8')
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    if zc_validation():
        # sql插入语句
        # 获取系统当前日期
        curr_time1 = time.localtime()
        time_str1 = time.strftime("%Y-%m-%d,%H:%M:%S", curr_time1)

        if len(zc_e4.get()) != 0:
            sql2 = (zc_e1.get(), zc_e2.get(), zc_e3.get(), zc_e4.get(), zc_e5.get())
        else:
            sql2 = (zc_e1.get(), zc_e2.get(), zc_e3.get(), time_str1, zc_e5.get())

        cursor.execute("INSERT INTO PAY(ZC_BT,ZC_JE,ZC_FS,ZC_SJ,ZC_BZ) VALUES( %s, %s, %s, %s,%s)", sql2)
        # 表中插入数据
        tree1.insert('', "end", values=(str(zc_e1.get()), str(zc_e2.get()), str(zc_e3.get()), str(zc_e4.get()), str(zc_e5.get())))
        message2['text'] = "添加成功"
        # 添加成功后清空文本框
        zc_biao_ti.set("")
        zc_price.set("")
        zc_way.set("")
        zc_memo.set("")
    else:
        message2['text'] = "标题,金额,方式,不能为空"
        # 提交到数据库执
    db.commit()
    db.close()










